from osv import fields,osv
import mx.DateTime
import tools.sql
from tools.translate import _

class kdvn_report_job_and_contract_to_client(osv.osv):
    _name='kdvn.report.job.and.contract.to.client'
    _description='KDVN Report Job And Contract To Client'
    _auto=False
    _columns={
                'project_id':fields.many2one('kdvn.project','Project'),
                'contract_id':fields.many2one('kdvn.contract.client','Contract'),
                'quotation_number':fields.char('Quotation Number',size=25),
                'work_description':fields.char('Work Description',size=255),
                'submitted_amount':fields.float('Submitted Amount'),
                'approved_amount':fields.float('Approved Amount'),
                'contract_currency':fields.char('Contract Currency',size=3),
                'contracted_amount_usd':fields.float('Contracted Amount(USD)'),
                'contracted_amount_vnd':fields.float('Contracted Amount (VND)'),
                'contracted_amount_equivalent_to_vnd':fields.float('Contracted Amt Equivalent To VND',digits=(16,0)),
                'tca_usd':fields.float('T.C.A USD'),
                'tca_vnd':fields.float('T.C.A VND'),
                'project_for_ref':fields.many2one('kdvn.project','Project For Ref'),
                'invoiceno':fields.char('Invoice No.',size=25),
                'invoicedate':fields.date('Invoice Date'),
                'claim_amount_usd':fields.float('Claim Amount USD'),
                'claim_amount_vnd':fields.float('Claim Amount VND'),
                'claim_total_amount':fields.float('Claim Total Amount'),
                'received_date':fields.date('Recv. Date'),
                'received_usd':fields.float('Received USD'),
                'received_vnd':fields.float('Received VND'),
                'received_equivalent_vnd':fields.float('Received Equivalent VND'),
                'received_total_amount':fields.float('Received Total Amount'),
                'payment_term':fields.char('Payment Term',size=255),
                'started_date':fields.date('Started Date'),
                'completion_date':fields.date('Completion Date'),
                'sort_field':fields.integer("Sort Field")
                #'sort_field':fields.char('Sort Field',size=150)
              }
    def init1(self,cr):
        tools.sql.drop_view_if_exists(cr,'kdvn_report_job_and_contract_to_client')
        cr.execute("create or replace view kdvn_report_job_and_contract_to_client as \
                        Select\
                        (kp.id::text || coalesce(coalesce(so_contracted.id,so_sumbitting.id),0)::text || coalesce(kpfc.id,1)::text)::bigint as id,\
                        kp.id as project_id,\
                        case when so_sumbitting.contract_id is null then so_contracted.contract_id else so_sumbitting.contract_id end as contract_id,\
                        coalesce(so_sumbitting.name,so_contracted.name) as quotation_number,\
                        coalesce(so_sumbitting.description,so_contracted.description) as work_description,\
                        so_sumbitting.amount_total as submitted_amount,\
                        case when \
                        project_type='E' then so_sumbitting.q_amount_total_e \
                        else\
                        so_sumbitting.q_amount_total_m end as approved_amount,\
                        pc.name as contract_currency,\
                        case when pc.name='USD' then \
                        case when project_type='E' then kcc.contract_amount_e else kcc.contract_amount_m end \
                        else\
                        0\
                        end as contracted_amount_usd,\
                        case when pc.name='VND' then \
                        case when project_type='E' then kcc.contract_amount_e else kcc.contract_amount_m end \
                        else\
                        0\
                        end as contracted_amount_vnd,\
                        pc.rate*(case when project_type='E' then kcc.contract_amount_e else kcc.contract_amount_m end)/pcvnd.rate as contracted_amount_equivalent_to_vnd,\
                        case when pc.name='USD' then contract_amount_e+contract_amount_m else 0 end as tca_usd,\
                        case when pc.name='VND' then contract_amount_e+contract_amount_m else 0 end as tca_vnd,\
                        case when project_type='E' then so_contracted.project_m_id else so_contracted.project_e_id end as project_for_ref,\
                        kpfc.number as invoiceno,\
                        kpfc.date as invoicedate,\
                        case when pc.name='USD' then \
                        case when project_type='E' then kpfc.amount_e else kpfc.amount_m end \
                        else\
                        0\
                        end as claim_amount_usd,\
                        case when pc.name='VND' then \
                        case when project_type='E' then kpfc.amount_e else kpfc.amount_m end \
                        else\
                        0\
                        end as claim_amount_vnd,\
                        kr.date as received_date,\
                        coalesce(kpfc.amount_e,0) + coalesce(kpfc.amount_m,0) as claim_total_amount,\
                        case when pc.name='USD' and coalesce(actual_amount,0)>0 then case when project_type='E' then kpfc.amount_e else kpfc.amount_m end else 0 end as received_usd,\
                        case when pc.name='VND' and coalesce(actual_amount,0)>0 then case when project_type='E' then kpfc.amount_e else kpfc.amount_m end else 0 end as received_vnd,\
                        case when coalesce(actual_amount,0)>0 then case when project_type='E' then kpfc.amount_e else kpfc.amount_m end*kr.rate else 0 end as received_equivalent_vnd,\
                        kr.amount as received_total_amount,\
                        kptl.name as payment_term,\
                        kcc.started_date,\
                        kcc.completion_date\
                        from\
                        kdvn_project kp\
                        left join\
                        sale_order so_sumbitting on ((kp.id = project_e_id and project_type='E') or (kp.id=project_m_id and project_type='M')) and so_sumbitting.state!='cancel' and so_sumbitting.contract_id is null\
                        left join\
                        sale_order so_contracted on ((kp.id = so_contracted.project_e_id and project_type='E') or (kp.id=so_contracted.project_m_id and project_type='M')) and so_contracted.state not in ('draft','cancel') and so_contracted.contract_id is not null\
                        \
                        left join\
                        kdvn_payment_from_client kpfc on so_contracted.contract_id = kpfc.contract_id and kpfc.state not in ('draft','cancel')\
                        left join\
                        kdvn_payment_term_line kptl on payment_term_line_id=kptl.id\
                        left join\
                        kdvn_received kr on kpfc.id = kr.payment_id\
                        \
                        left join\
                        project_currency pc on so_contracted.contract_id = pc.contract_id and default_curr=True\
                        left join\
                        kdvn_contract_client kcc on so_contracted.contract_id = kcc.id\
                        left join\
                        project_currency pcvnd on so_contracted.contract_id = pcvnd.contract_id and pcvnd.name='VND'")
kdvn_report_job_and_contract_to_client()

class kdvn_project(osv.osv):
    _name = "kdvn.project"
    _inherit = "kdvn.project"
  
    _columns={
          #   'kdvn_job_and_contract_list_ids':fields.one2many('kdvn.report.job.and.contract.to.client','project_id','KDVN Project - Contract list'),
             }
kdvn_project()